﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class TheKhachHangDB
    {
        DataProvider dp = new DataProvider();

        public DataTable dLayDSKhThanThiet()
        {
            string sql = "select MaKH, TenKH, DiaChiKH, SdtKH, CmndKH, DiemTichLuy, TenNV from TheKhachHang, NhanVien where MaNV = NVCapThe";
            return dp.ExecuteQuery(sql);
        }

        public DataTable dTimKhThanThiet(string tenKH)
        {
            string sql = "select MaKH, TenKH, DiaChiKH, SdtKH, CmndKH, DiemTichLuy, TenNV from TheKhachHang, NhanVien where MaNV = NVCapThe and TenKH like N'%" + tenKH + "%'";
            return dp.ExecuteQuery(sql);
        }

        public DataTable dLayTheKHTheoMa(string makh)
        {
            string sql = "SELECT MaKH, TenKH, CmndKH, DiemTichLuy FROM dbo.TheKhachHang WHERE MaKH like '" + makh + "%' and DiemTichLuy >= 20";
            return dp.ExecuteQuery(sql);
        }

        public bool dCapTheKhachHang(TheKhachHang tkh, int manv)
        {
            string sql = "insert into TheKhachHang values(N'" + tkh.TenKh + "', N'" + tkh.Diachi + "', '" + tkh.Sodienthoai + "', '" + tkh.CMND + "', 20, 6)";
            return dp.ExecuteNonQuery(sql);
        }

        public bool bCapNhatDiemTichLuy(int makh, int diemtichluy)
        {
            string sql = "update TheKhachHang set DiemTichLuy = " + diemtichluy + " + (select DiemTichLuy from TheKhachHang where MaKH = " + makh + ") where MaKH =" + makh;
            return dp.ExecuteNonQuery(sql);
        }
    }
}
